package users;

import utils.Utils;

import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class ManagerFun {
    //判断是否为纯数字字符串
    public static boolean isAllDigits(String str) {
        return str != null && str.matches("\\d+");
    }
    //查询图书编号为id的图书
    public static void queryone(String id, DefaultTableModel stumodel)throws Exception{
        if(!isAllDigits(id)){
            throw new Exception();
        }
        stumodel.setRowCount(0);
        int id_1 = Integer.parseInt(id);
        System.out.println("querystu成功！");
        //连接mysql
        Connection connection= Utils.getConnection();
        //定义sql语句
        String sql="select * from book where id like ? ";
        PreparedStatement stmt = connection.prepareStatement(sql);
        stmt.setString(1, "%" + id + "%");
        ResultSet rs = stmt.executeQuery();
        while(rs.next()){
            int id_2 = rs.getInt("id");
            String name = rs.getString("name");
            String type = rs.getString("type");
            String publish = rs.getString("publish");
            String author = rs.getString("author");
            Double price = rs.getDouble("price");
            int num = rs.getInt("num");
            stumodel.addRow(new Object[]{Boolean.FALSE,id_2,name,type,publish,author,price,num});
        }
        Utils.Close(stmt,connection);
    }

    //查询全部图书信息（
    public static void queryall(DefaultTableModel stumodel)throws Exception{
        //System.out.println("querystu成功！");
        //连接mysql
        stumodel.setRowCount(0);
        Connection connection= Utils.getConnection();
        //定义sql语句
        String sql="select * from book";
        Statement stmt = connection.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        while(rs.next()){
            int id_2 = rs.getInt("id");
            String name = rs.getString("name");
            String type = rs.getString("type");
            String publish = rs.getString("publish");
            String author = rs.getString("author");
            Double price = rs.getDouble("price");
            int num = rs.getInt("num");
            stumodel.addRow(new Object[]{Boolean.FALSE,id_2,name,type,publish,author,price,num});
        }
        Utils.Close(stmt,connection);
    }
    //添加图书
    public static void addbook(String name,String type,String publish,String author,String price,String num)throws Exception{
        Connection connection= Utils.getConnection();
        //定义sql语句
        String sql="insert into book(name,type,publish,author,price,num) values(?,?,?,?,?,?)";
        PreparedStatement stmt = connection.prepareStatement(sql);
        stmt.setString(1,name);
        stmt.setString(2,type);
        stmt.setString(3,publish);
        stmt.setString(4,author);
        stmt.setDouble(5, Double.parseDouble(price));
        stmt.setInt(6, Integer.parseInt(num));
        stmt.executeUpdate();
        Utils.Close(stmt,connection);
    }
    //一键审核
    public static void checkall(JTable table,String username)throws Exception{

        if(!UsersFun.ischeck(table)){
            return;
        }
        //连接mysql
        Connection connection= Utils.getConnection();
        //定义sql语句
        String sql="update application set result='1',processor_name=? where id=?";
        String sql2="UPDATE user set token='1' where username=? and token='-1' ";
        PreparedStatement stmt = connection.prepareStatement(sql);
        PreparedStatement stmt2=connection.prepareStatement(sql2);

        for (int i = 0; i < table.getRowCount(); i++) {
            //获取复选框状态（是否选中）
            Boolean isSelected = (Boolean) table.getValueAt(i, 0);
            if (isSelected) {
                int id= (int) table.getValueAt(i, 1);
                stmt2.setString(1, table.getValueAt(i,2).toString());
                stmt.setString(1,username);
                stmt.setInt(2,id);
                //执行sql语句
                stmt.executeUpdate();
                stmt2.executeUpdate();
            }
        }
        Utils.Close(stmt,connection);
        Utils.Close(stmt2,connection);
    }

    public static void  refuseall(JTable table,String username)throws Exception{
        if(!UsersFun.ischeck(table)){
            return;
        }
        //连接mysql
        Connection connection= Utils.getConnection();
        //定义sql语句
        String sql="update application set result='-1',processor_name=? where id=?";
        PreparedStatement stmt = connection.prepareStatement(sql);
        for (int i = 0; i < table.getRowCount(); i++) {
            //获取复选框状态（是否选中）
            Boolean isSelected = (Boolean) table.getValueAt(i, 0);
            if (isSelected) {
                int id= (int) table.getValueAt(i, 1);
                stmt.setString(1,username);
                stmt.setInt(2,id);
                //执行sql语句
                stmt.executeUpdate();
            }
        }
        Utils.Close(stmt,connection);
    }
    public static void deleteall(JTable table)throws Exception{
        if(!UsersFun.ischeck(table)){
            return;
        }
        //连接mysql
        Connection connection= Utils.getConnection();
        //定义sql语句
        String sql="update book set num=0 where id=?";
        PreparedStatement stmt = connection.prepareStatement(sql);
        for (int i = 0; i < table.getRowCount(); i++) {
            //获取复选框状态（是否选中）
            Boolean isSelected = (Boolean) table.getValueAt(i, 0);
            if (isSelected) {
                stmt.setInt(1,(Integer) table.getValueAt(i,1));
                //执行sql语句
                stmt.executeUpdate();
            }
        }
        Utils.Close(stmt,connection);
    }
    public static void updatebook(int id,String name,String type,String publish,String author,String price,String num)throws Exception{
        //连接mysql
        Connection connection= Utils.getConnection();
        //定义sql语句
        String sql="update book set name=?,type=?,publish=?,author=?,price=?,num=? where id=?";
        PreparedStatement stmt = connection.prepareStatement(sql);
        stmt.setInt(7, id);
        stmt.setString(1,name);
        stmt.setString(2,type);
        stmt.setString(3,publish);
        stmt.setString(4,author);
        stmt.setDouble(5, Double.parseDouble(price));
        stmt.setInt(6, Integer.parseInt(num));
        stmt.executeUpdate();
        Utils.Close(stmt,connection);
    }
    public static void updatenum(int id,int num)throws Exception{
        //连接mysql
        Connection connection= Utils.getConnection();
        //定义sql语句
        String sql="update book set num=num-? where id=?";
        PreparedStatement stmt = connection.prepareStatement(sql);
        stmt.setInt(2, id);
        stmt.setInt(1,num);
        stmt.executeUpdate();
        Utils.Close(stmt,connection);
    }
    public static void registermessage(DefaultTableModel model)throws Exception{
        //连接mysql
        model.setRowCount(0);
        Connection connection= Utils.getConnection();
        //定义sql语句
        String sql="select * from application ";
        Statement stmt = connection.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        while(rs.next()){
            int id = rs.getInt("id");
            String applicant_name = rs.getString("applicant_name");
            String processor_name = rs.getString("processor_name");
            String result = rs.getString("result");
            model.addRow(new Object[]{Boolean.FALSE,id,applicant_name,processor_name,result});
        }
        Utils.Close(stmt,connection);
    }
    public static void registerone(DefaultTableModel model,String registername)throws Exception{
        model.setRowCount(0);
        Connection connection= Utils.getConnection();
        //定义sql语句
        String sql="select * from application where applicant_name like ?";
        PreparedStatement stmt = connection.prepareStatement(sql);
        stmt.setString(1,"%" + registername + "%");
        ResultSet rs=stmt.executeQuery();
        while(rs.next()){
            int id = rs.getInt("id");
            String applicant_name = rs.getString("applicant_name");
            String processor_name = rs.getString("processor_name");
            String result = rs.getString("result");
            model.addRow(new Object[]{Boolean.FALSE,id,applicant_name,processor_name,result});
        }
        Utils.Close(stmt,connection);
    }
}
